using Rhino.Etl.Core;
using Rhino.Etl.Core.Infrastructure;

namespace EtlDemo.Rhino.JobCounts
{
    public class Staging : WithLoggingMixin
    {
        public void CreateStagingTable()
        {
            Info("Recreating JobCounts_Stage table");
            Use.Transaction("destination", cmd =>
            {
                cmd.CommandText =
                    @"
                    if object_id('JobCounts_Stage') is not null
                        drop table JobCounts_Stage
                    create table JobCounts_Stage (
                        [Affiliate] [nvarchar](20) unique NOT NULL,
                        [JobCount] [int] NULL
                    )";

                cmd.ExecuteNonQuery();
            });
            Info("Recreated JobCounts_Stage table");
        }

        public void UpsertFromStagingTable()
        {
            Info("Upsert starting");
            Use.Transaction("destination", cmd =>
            {
                cmd.CommandText =
                    @"merge into [JobCounts] using [JobCounts_Stage]
                    on JobCounts.Affiliate = JobCounts_Stage.Affiliate
                    when matched then 
	                    update set JobCount = JobCounts_Stage.JobCount
                    when not matched then 
	                    insert (Affiliate, [JobCount])
                            values
                                (
                                  JobCounts_Stage.Affiliate
                                , JobCounts_Stage.JobCount
                                )
                    when not matched by source 
                        then delete;";

                cmd.ExecuteNonQuery();
            });
            Info("Upsert completed");
        }
    }
}